import requests
import sqlite3
from lxml import etree


class DataFilling:

    def get_category_data(self):
        res = requests.get("https://www.xiaoshuopu.com/")
        content = res.text
        tree = etree.HTML(content)
        # 分类数据
        category = tree.cssselect(".update li .ul1")
        category_datas = []
        # index = 1
        for ct in category:
            if ct.text is not None:
                # index += 1
                # category_datas.append((index, ct.text[:6]))
                category_datas.append(ct.text[1:5])
        return category_datas

    def get_book_name_data(self):
        res = requests.get("https://www.xiaoshuopu.com/")
        content = res.text
        tree = etree.HTML(content)
        # 书名数据
        book_name = tree.cssselect(".update li .ul1 a")
        book_name_datas = []

        for bn in book_name:
            if bn.text is not None:
                book_name_datas.append(f"《{bn.text}》")
        return book_name_datas

    def get_latest_chapter_data(self):
        res = requests.get("https://www.xiaoshuopu.com/")
        content = res.text
        tree = etree.HTML(content)
        # 最新章节数据
        latest_chapter = tree.cssselect(".update li .ul2 a")
        latest_chapter_datas = []

        for lc in latest_chapter:
            if lc.text is not None:
                latest_chapter_datas.append(lc.text)
        return latest_chapter_datas

    def get_author_data(self):
        res = requests.get("https://www.xiaoshuopu.com/")
        content = res.text
        tree = etree.HTML(content)
        # 作者数据
        author = tree.cssselect(".update li p:last-child")
        author_datas = []

        for ao in author:
            if ao.text is not None:
                author_datas.append(ao.text)
        return author_datas

    def get_last_update_data(self):
        res = requests.get("https://www.xiaoshuopu.com/")
        content = res.text
        tree = etree.HTML(content)
        # 最新更新日期(简)数据
        last_update = tree.cssselect(".update li ")
        last_update_datas = []

        for lu in last_update:
            for child in lu:
                if child.tail:
                    last_update_datas.append(child.tail)
        return last_update_datas

    def save_author_data(self, author_datas):
        con = sqlite3.connect("../mysite/db.sqlite3")
        cur = con.cursor()
        cur.executemany('insert into main_author (name) values (?)', author_datas)
        con.commit()
        cur.close()
        con.close()

    def save_book_data(self, books_data):
        con = sqlite3.connect("../mysite/db.sqlite3")
        cur = con.cursor()
        cur.execute('PRAGMA foreign_keys = OFF;')
        cur.executemany(
            'insert into main_book (category_id, name, latest_chapter, author_id, last_update_simple) values (?,?,?,?,?)',
            books_data)
        cur.execute('PRAGMA foreign_keys = ON;')

        con.commit()
        cur.close()
        con.close()


def get_category_id(category_datas):
    con = sqlite3.connect("../mysite/db.sqlite3")
    cur = con.cursor()
    cur.execute("SELECT id FROM main_category WHERE name = ?", (category_datas,))
    row = cur.fetchone()
    if row:
        return row[0]
    else:
        return None

def get_author_id(author_datas):
    con = sqlite3.connect("../mysite/db.sqlite3")
    cur = con.cursor()
    cur.execute("SELECT id FROM main_author WHERE name = ?", (author_datas,))
    row = cur.fetchone()
    if row:
        return row[0]
    else:
        return None





df = DataFilling()

# 先添加作者表
author_datas = df.get_author_data()
author_datas = list(zip(author_datas))
df.save_author_data(author_datas)

# 再添加分类表（手动添加过）





# 转换图书分类的外键值
category_datas = df.get_category_data()
print(category_datas)
for i in range(len(category_datas)):
    category_datas2 = get_category_id(category_datas[i])
    category_datas[i] = category_datas2
    # print(category_datas2)
print(category_datas)

# 转换作者的外键值
author_datas = df.get_author_data()
print(author_datas)
for j in range(len(author_datas)):
    author_datas2 = get_author_id(author_datas[j])
    author_datas[j] = author_datas2
    # print(author_datas2)
print(author_datas)



# 添加整体数据到main_book表
book_name_data = df.get_book_name_data()
latest_chapter = df.get_latest_chapter_data()
last_update_data = df.get_last_update_data()
books_data = list(zip(category_datas, book_name_data, latest_chapter, author_datas, last_update_data))
print(books_data)
df.save_book_data(books_data)








